﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace pTools.cMethods
{
    public class DBHelper
    {
        static string barcon = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string connectionString = "";
        public DataTable getData(string strsql, string baseCode = "")
        {

            connectionString = barcon;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = strsql;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable datatable = new DataTable();
                    adapter.Fill(datatable);
                    return datatable;
                }
            }
        }
        public string UpdateData(string sql)
        {
            connectionString = barcon;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())//创建命令对象SqlCommand
                {
                    cmd.Connection = conn;//设置连接对象
                    cmd.CommandType = CommandType.Text;//设置Command对象执行语句的类型
                    cmd.CommandText = sql;//设置执行的语句
                    return cmd.ExecuteNonQuery().ToString();//ExecuteNonQuery返回执行SQL语句，受影响的行数
                }
            }
        }
        /// <summary>
        /// 多表事务更新方法
        /// </summary>
        /// <param name="cmdlist">sql集合</param>
        /// <returns></returns>
        public int TransUpdate(List<string> cmdlist)
        {
            int jg = 0;
            try
            {
                connectionString = barcon;
                using (SqlConnection connection = new SqlConnection())
                {
                    connection.ConnectionString = connectionString;
                    connection.Open();
                    using (SqlTransaction myTran = connection.BeginTransaction())//创建事物
                    {
                        try
                        {
                            SqlCommand command = new SqlCommand();
                            command.Transaction = myTran;
                            command.Connection = connection;
                            for (int i = 0; i < cmdlist.Count; i++)
                            {
                                command.CommandType = CommandType.Text;
                                command.CommandText = cmdlist[i].ToString();
                                command.Parameters.Clear();//清空参数集合
                                command.ExecuteNonQuery();
                            }
                            myTran.Commit();
                            jg = 1;
                        }
                        catch (Exception ex)
                        {
                            jg = 0;
                            myTran.Rollback();
                        }

                    }

                }
            }
            catch (Exception ex)
            {
                jg = 0;
            }
            return jg;
        }

    }
}
